package com.bestcem.xm.qdes.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.http.HtmlUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.bestcem.xm.common.core.domain.web.XmResultJson;
import com.bestcem.xm.common.core.exception.BusinessException;
import com.bestcem.xm.common.core.uitls.ExcelExportUtil;
import com.bestcem.xm.common.core.uitls.FileUtil;
import com.bestcem.xm.component.mybatis.service.impl.XmServiceImpl;
import com.bestcem.xm.component.security.context.SecurityContextHolder;
import com.bestcem.xm.qdes.controller.param.ExportQuotaParam;
import com.bestcem.xm.qdes.controller.param.QuotaCreateParam;
import com.bestcem.xm.qdes.controller.param.QuotaDeleteParam;
import com.bestcem.xm.qdes.controller.param.QuotaNameModifyParam;
import com.bestcem.xm.qdes.controller.vo.*;
import com.bestcem.xm.qdes.entity.QdesProject;
import com.bestcem.xm.qdes.entity.QdesQstruct;
import com.bestcem.xm.qdes.entity.QdesQuestion;
import com.bestcem.xm.qdes.entity.QdesQuota;
import com.bestcem.xm.qdes.grpc.client.SurveyGrpcClient;
import com.bestcem.xm.qdes.mapper.QdesProjectMapper;
import com.bestcem.xm.qdes.mapper.QdesQstructMapper;
import com.bestcem.xm.qdes.mapper.QdesQuestionMapper;
import com.bestcem.xm.qdes.mapper.QdesQuotaMapper;
import com.bestcem.xm.qdes.service.QdesProjectService;
import com.bestcem.xm.qdes.service.QdesQuotaService;
import com.bestcem.xm.survey.grpc.v1.services.QuotaCondition;
import com.google.common.collect.Lists;
import lombok.NonNull;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bson.types.ObjectId;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

import static com.bestcem.xm.qdes.grpc.v1.services.QuotaQuotaType.QUOTA_QUOTA_TYPE_CROSS;
import static com.bestcem.xm.qdes.grpc.v1.services.QuotaQuotaType.QUOTA_QUOTA_TYPE_NORMAL;


/**
 * 描述：配额
 * 作者：杨海奇
 * 时间：2022/8/25 17:33
 */
@Slf4j
@RequiredArgsConstructor
@Service
public class QdesQuotaServiceImpl extends XmServiceImpl<QdesQuotaMapper, QdesQuota> implements QdesQuotaService {
    private @NonNull QdesProjectMapper qdesProjectMapper;
    private @NonNull QdesQstructMapper qdesQstructMapper;
    private @NonNull QdesProjectService qdesProjectService;
    private @NonNull SurveyGrpcClient surveyGrpcClient;
    private @NonNull QdesQuestionMapper qdesQuestionMapper;

    /**
     * 分页配额查询
     *
     * @param page
     * @param projectId
     * @return
     */
    @Override
    public QdesQuotaPageVo getQuotaList(Page<QdesQuota> page, String projectId) {
        QdesQuotaPageVo pageVo = new QdesQuotaPageVo();
        pageVo.setPage(page.getCurrent());

        //查询
        Page<QdesQuota> qdesQuotaPage = baseMapper.selectPage(page,
                new LambdaQueryWrapper<QdesQuota>()
                        .eq(QdesQuota::getProjectId, projectId)
                        .orderByDesc(QdesQuota::getUpdatedDt)
        );

        //判空
        List<QdesQuota> records = qdesQuotaPage.getRecords();
        if (CollUtil.isEmpty(records)) {
            pageVo.setTotalCount(0);
            pageVo.setRows(new ArrayList<>());
            return pageVo;
        }

        //组装
        List<QdesQuotaVo> quotaVoList = records.stream().map(qdesQuota -> {
            QdesQuotaVo qdesQuotaVo = new QdesQuotaVo();
            BeanUtil.copyProperties(qdesQuota, qdesQuotaVo);
            return qdesQuotaVo;
        }).collect(Collectors.toList());
        pageVo.setTotalCount(qdesQuotaPage.getTotal());
        pageVo.setRows(quotaVoList);
        return pageVo;
    }

    /**
     * 配额详情
     *
     * @param quotaId
     * @return
     */
    @Override
    public QdesQuotaVo getQuotaDetail(String quotaId) {

        // 获取单个
        QdesQuota qdesQuota = baseMapper.selectById(quotaId);
        QdesQuotaVo qdesQuotaVo = new QdesQuotaVo();
        if (qdesQuota != null) {
            BeanUtil.copyProperties(qdesQuota, qdesQuotaVo);
        }
        return qdesQuotaVo;
    }

    /**
     * 获取配额的名称
     *
     * @param projectId
     * @param quotaId
     * @return
     */
    @Override
    public QuotaNameVo getQuotaName(String projectId, String quotaId) {

        //查询
        QdesQuota qdesQuota = baseMapper.selectById(quotaId);
        if (qdesQuota == null) {
            throw new BusinessException("未查到配额");
        }
        return new QuotaNameVo(qdesQuota.getName());
    }

    /**
     * 设置配置名称
     *
     * @param projectId
     * @param quotaId
     * @param param
     * @return
     */
    @Override
    public QuotaIdVo setQuotaName(String projectId, String quotaId, QuotaNameModifyParam param) {
        //更新
        QdesQuota entity = new QdesQuota();
        entity.setId(quotaId);
        entity.setName(param.getName());
        int i = baseMapper.updateById(entity);
        log.info("配额：{}修改名称为：{}。更新结果：{}", quotaId, param.getName(), i);
        if (i == 0) {
            throw new BusinessException("更新失败，未匹配到配额信息");
        }

        //
        return new QuotaIdVo(quotaId);
    }

    /**
     * 批量删除配额
     *
     * @param projectId
     * @param quotaDeleteParam
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public DeletedCountVo deleteQuotas(String projectId, QuotaDeleteParam quotaDeleteParam) {
        //校验
        List<String> idList = quotaDeleteParam.getIdList();
        if (CollUtil.isEmpty(idList)) {
            //throw new BusinessException("配额id列表为空");
            return new DeletedCountVo(0);
        }
        log.info("projectId={},quotaDeleteVo={}", projectId, JSON.toJSONString(quotaDeleteParam));

        //查询
        List<QdesQuota> qdesQuotas = baseMapper.selectBatchIds(idList);
        if (CollUtil.isEmpty(qdesQuotas)) {
            return new DeletedCountVo(0);
        }
        //删除子表及子子表
        surveyGrpcClient.deleteSurveyQuotas(qdesQuotas.stream().map(QdesQuota::getId).collect(Collectors.toList()));

        //删除主表
        int i = baseMapper.deleteBatchIds(idList);
        log.info("删除结果：{}", i);
        return new DeletedCountVo(i);
    }

    /**
     * 创建配额
     *
     * @param projectId
     * @param quotaCreateParam
     * @return
     */
    @Override
    public QuotaIdVo createQuota(String projectId, QuotaCreateParam quotaCreateParam) {
        //校验
        QdesProject qdesProject = qdesProjectMapper.selectById(projectId);
        if (qdesProject == null) {
            throw new BusinessException("项目编号未查到项目信息");
        }

        //保存
        QdesQuota qdesQuota = new QdesQuota();
        qdesQuota.setId(new ObjectId().toHexString());
        qdesQuota.setCreatedId(SecurityContextHolder.getUserId());
        qdesQuota.setCreatedDt(LocalDateTime.now());
        //
        if (quotaCreateParam.getQuotaType() == null) {
            qdesQuota.setQuotaType(quotaCreateParam.getType());
        } else {
            qdesQuota.setQuotaType(quotaCreateParam.getQuotaType());
        }
        qdesQuota.setName(quotaCreateParam.getName());
        qdesQuota.setProjectId(projectId);
        // 设置默认值
        qdesQuota.setQuotaCondList("[]");
        qdesQuota.setAvaQgidList("[]");
        qdesQuota.setRow1("[]");
        qdesQuota.setRow2("[]");
        qdesQuota.setCol1("[]");
        qdesQuota.setCol2("[]");
        baseMapper.insert(qdesQuota);
        //
        return new QuotaIdVo(qdesQuota.getId());
    }

    @Override
    public void exportQuota(String projectId, ExportQuotaParam exportQuotaParam, HttpServletResponse response) {
        // 生成配额excel文件
        // param idList: idList为配额ID的lis；t，为空表示生成所有
        //校验
        QdesProject project = qdesProjectMapper.selectById(projectId);
        if (project == null) {
            throw new BusinessException("项目编号未查到项目信息");
        }

        String fileName = project.getCode() + "_quota";
        String sheet = "quota list";
        // 处理普通配额
        LambdaQueryWrapper<QdesQuota> normalWrapper = new LambdaQueryWrapper<>();
        normalWrapper.eq(QdesQuota::getProjectId, projectId).eq(QdesQuota::getQuotaType, QUOTA_QUOTA_TYPE_NORMAL.getNumber());
        if (CollectionUtils.isNotEmpty(exportQuotaParam.getIdList())) {
            normalWrapper.in(QdesQuota::getId, exportQuotaParam.getIdList());
        }
        List<QdesQuota> normalQdesQuotas = baseMapper.selectList(normalWrapper);
        List<JSONObject> questionCidGidDict = getQuestionCidGidDict(project);
        Map<String, JSONObject> qcidDict = new HashMap<>();
        Map<String, JSONObject> qgidDict = new HashMap<>();
        List<List<String>> allData = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(questionCidGidDict)) {
            questionCidGidDict.forEach(js -> {
                qcidDict.put(js.getString("cid"), js);
                qgidDict.put(js.getString("gid"), js);
            });
        } else {
            exportWithDynamicData(fileName, allData, sheet, response);
            return;
        }
        for (QdesQuota quota : normalQdesQuotas) {
            writeNormalQuota(allData, quota, qgidDict);
        }

        // 处理交叉配额
        LambdaQueryWrapper<QdesQuota> crosslWrapper = new LambdaQueryWrapper<>();
        crosslWrapper.eq(QdesQuota::getProjectId, projectId).eq(QdesQuota::getQuotaType, QUOTA_QUOTA_TYPE_CROSS.getNumber());
        if (CollectionUtils.isNotEmpty(exportQuotaParam.getIdList())) {
            crosslWrapper.in(QdesQuota::getId, exportQuotaParam.getIdList());
        }
        List<QdesQuota> crossQdesQuotas = baseMapper.selectList(crosslWrapper);
        for (QdesQuota quota : crossQdesQuotas) {
            writeCrossQuota(allData, quota, qgidDict);
        }

        exportWithDynamicData(fileName, allData, sheet, response);
    }

    @Override
    public XmResultJson<Object> importQuota(String projectId, MultipartFile files) {
        // 校验参数
        QdesProject project = qdesProjectMapper.selectById(projectId);
        if (project == null) {
            throw new BusinessException("项目编号未查到项目信息");
        }
        if (ObjectUtil.isEmpty(files)) {
            log.error("导入的文件不存在");
            // 失败的话 先删除锁
            throw new BusinessException("File is not exist");
        }
        if (!files.isEmpty()) {
            // 获取文件名称
            String fileName = files.getOriginalFilename();

            String fileType = FileUtil.getFileExtension(fileName);
            if (!"xls".equals(fileType) && !"xlsx".equals(fileType)) {
                // 文件类型不正确
                log.error("【qdes excel 导入】文件类型不正确 期望是 {} 实际是 {}", "xls或者xlsx", fileType);
                throw new BusinessException("pload Quota File is not a excel");
            }
            // 获取文件大小
            long size = files.getSize();
            if (!FileUtil.checkFileSize(size, 10, FileUtil.M)) {
                // 限制10M
                throw new BusinessException("文件过大");
            }
            // 根据excel导入
            try (InputStream inputStream = files.getInputStream()) {
                List<String> errorMsg = new ArrayList<>();
                List<JSONObject> quotaList = importQuotaData(inputStream, project, errorMsg);
                // 检查配额题目是否已经用过
                List<String> resultQuotaidList = new ArrayList<>();
                for (JSONObject dataList : quotaList) {
                    List<String> qgidList = dataList.getJSONArray("qgid_list").toJavaList(String.class);
                    List<String> qcidList = dataList.getJSONArray("qcid_list").toJavaList(String.class);
                    String quotaType = dataList.getString("quota_type");

                    boolean isNormal = "普通配额".equals(quotaType);
                    Integer count = surveyGrpcClient.countSurveyQuota(projectId, qgidList, isNormal);
                    if (count != null && count > 0) {
                        errorMsg.add(String.format("题目：%s, 已经配置过配额。", String.join(",", qcidList)));
                    }
                }
                if (CollectionUtils.isNotEmpty(errorMsg)) {
                    try {
                        inputStream.close();
                    } catch (IOException ex) {
                        log.error("Close the inputStream error, e = ", ex);
                    }
                    return XmResultJson.fail(400, String.join(",", errorMsg));
                }

                for (JSONObject dataDict : quotaList) {
                    String title = dataDict.getString("title");
                    String quotaType = dataDict.getString("quota_type");
                    String checkpointGids = dataDict.getString("checkpoints");
                    JSONArray contentList = Optional.ofNullable(dataDict.getJSONArray("content_list"))
                            .orElse(new JSONArray());
                    if ("普通配额".equals(quotaType)) {
                        // 1.创建普通配额
                        QdesQuota quota = new QdesQuota();
                        quota.setName(title);
                        quota.setProjectId(projectId);
                        quota.setQuotaType(QUOTA_QUOTA_TYPE_NORMAL.getNumber());
                        // 设置默认值
                        quota.setQuotaCondList("[]");
                        quota.setAvaQgidList("[]");
                        quota.setRow1("[]");
                        quota.setRow2("[]");
                        quota.setCol1("[]");
                        quota.setCol2("[]");
                        baseMapper.insert(quota);
                        // 2.创建配额条件及配额检查点
                        List<JSONObject> qList = new ArrayList<>();
                        for (int i = 0; i < contentList.size(); i++) {
                            JSONObject content = contentList.getJSONObject(i);
                            JSONObject contentJs = new JSONObject();
                            contentJs.put("title", HtmlUtil.cleanHtmlTag(content.getString("title")));
                            contentJs.put("q_gid", content.get("qgid"));
                            contentJs.put("o_gid", content.get("ogid"));
                            contentJs.put("num", content.get("num"));
                            contentJs.put("check_node", checkpointGids);
                            qList.add(contentJs);
                        }
                        createQuotaCondition(projectId, quota.getId(), qList);
                    } else {
                        // 1. 创建交叉配额
                        QdesQuota quota = new QdesQuota();
                        quota.setName(title);
                        quota.setProjectId(projectId);
                        quota.setQuotaType(QUOTA_QUOTA_TYPE_CROSS.getNumber());
                        // 设置默认值
                        quota.setQuotaCondList("[]");
                        quota.setAvaQgidList("[]");
                        quota.setRow1("[]");
                        quota.setRow2("[]");
                        quota.setCol1("[]");
                        quota.setCol2("[]");
                        baseMapper.insert(quota);
                        // 2. 创建配额条件及配额检查点
                        createCrossQuotaCondition(projectId, quota.getId(), checkpointGids, dataDict,
                                contentList);
                    }
                }
            } catch (IOException e) {
                log.error("读取文件失败", e);
                throw new BusinessException("读取文件异常");
            }
        } else {
            // 文件为空
            throw new BusinessException("文件为空");
        }
        return XmResultJson.success();
    }

    private void createQuotaCondition(String projectId, String quotaId, List<JSONObject> qList) {
        List<String> quotaCondIdList = new ArrayList<>();
        // 新增普通配额
        for (JSONObject qmap : qList) {
            String name = qmap.getString("title");
            String qGid = qmap.getString("q_gid");
            String oGid = qmap.getString("o_gid");
            JSONArray checkNode = qmap.getJSONArray("check_node");
            Integer num = qmap.getInteger("num");

            String quotaCondId = surveyGrpcClient.createSurveyQuota(projectId, quotaId, checkNode.toJSONString(), name);
            if (StringUtils.isNotEmpty(qGid)) {
                surveyGrpcClient.createQuotaGidList(quotaCondId, Integer.parseInt(qGid));
            }

            String qciId = surveyGrpcClient.createQuotaCheckItem(quotaCondId, projectId, num);
            if (qciId != null) {
                surveyGrpcClient.createQuotaCheckItemOptionCombies(qciId, String.format("%s_%s", qGid, oGid));

                if (StringUtils.isNotEmpty(oGid)) {
                    surveyGrpcClient.createQuotaCheckItemOptionList(qciId, Integer.valueOf(oGid));
                }
            }

            quotaCondIdList.add(quotaCondId);
        }

        // stat
        QdesQuota quota = baseMapper.selectById(quotaId);
        List<String> quotaCondList = JSONArray.parseArray(quota.getQuotaCondList(), String.class);
        quotaCondList = Optional.ofNullable(quotaCondList).orElse(new ArrayList<>());
        for (String quotaCondId : quotaCondIdList) {
            if (!quotaCondList.contains(quotaCondId)) {
                quotaCondList.add(quotaCondId);
            }
        }

        quota.setQuotaCondList(JSONArray.toJSONString(quotaCondList));
        baseMapper.updateById(quota);
        // end
        surveyGrpcClient.genQuotaNumDelay(projectId, quotaId);
        surveyGrpcClient.refreshQuotaProgress(quotaId);
    }

    private void createCrossQuotaCondition(String projectId, String quotaId, String checkpointGids, JSONObject dataDict,
                                           JSONArray contentList) {
        JSONArray row1 = rowColStripHtmltags(dataDict.getJSONArray("row1"));
        JSONArray row2 = rowColStripHtmltags(dataDict.getJSONArray("row2"));
        JSONArray col1 = rowColStripHtmltags(dataDict.getJSONArray("col1"));
        JSONArray col2 = rowColStripHtmltags(dataDict.getJSONArray("col2"));
        String avaQgidList = dataDict.getString("ava_qgid_list");
        // 创建交叉配额条件
        JSONObject param = new JSONObject();
        param.put("projectId", projectId);
        param.put("quotaId", quotaId);
        param.put("row1", row1);
        param.put("row2", row2);
        param.put("col1", col1);
        param.put("col2", col2);
        param.put("ava_qgid_list", avaQgidList);
        param.put("checkitem_nums", contentList);
        param.put("check_node", checkpointGids);
        surveyGrpcClient.surveyCreateCrossQuotaCondition(param);
    }

    private JSONArray rowColStripHtmltags(JSONArray row) {
        if (row == null || row.isEmpty()) {
            return new JSONArray();
        }
        JSONArray newCol = new JSONArray();
        for (JSONObject tmprow : row.toJavaList(JSONObject.class)) {
            tmprow.put("qtitle", HtmlUtil.cleanHtmlTag(Optional.ofNullable(tmprow.getString("qtitle")).orElse("")));
            tmprow.put("otitle", HtmlUtil.cleanHtmlTag(Optional.ofNullable(tmprow.getString("otitle")).orElse("")));
            newCol.add(tmprow);
        }
        return newCol;
    }

    /**
     * 导入配额
     *
     * @param inputStream inputStream
     * @param project     files
     * @param errorMsg    errorMsg
     */
    private List<JSONObject> importQuotaData(InputStream inputStream, QdesProject project, List<String> errorMsg) {
        try {
            List<JSONObject> quotaList = new ArrayList<>();
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
            int maxRow = sheet.getLastRowNum();
            List<Integer> quotaIndexList = new ArrayList<>();
            for (int i = 0; i <= maxRow; i++) {
                XSSFRow row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                String cell = null == row.getCell(1) ? "" : row.getCell(1).toString();
                if ("普通配额".equals(cell) || "交叉配额".equals(cell)) {
                    quotaIndexList.add(i);
                }
            }
            List<JSONObject> cidGidDict = getQuestionCidGidDict(project);
            Map<String, JSONObject> qcidDict = new HashMap<>();
            Map<String, JSONObject> qgidDict = new HashMap<>();
            if (CollectionUtils.isNotEmpty(cidGidDict)) {
                cidGidDict.forEach(js -> {
                    qcidDict.put(js.getString("cid"), js);
                    qgidDict.put(js.getString("gid"), js);
                });
            }

            for (int i = 0; i < quotaIndexList.size(); i++) {
                int j = quotaIndexList.get(i);
                JSONObject quotaDict = new JSONObject();
                String quotaType = sheet.getRow(j).getCell(1).toString();
                quotaDict.put("quota_type", quotaType);
                quotaDict.put("title", sheet.getRow(j).getCell(0).toString());
                quotaDict.put("checkpoints", new JSONArray());
                for (String ck : sheet.getRow(j).getCell(2).toString().split(",")) {
                    if (qcidDict.containsKey(ck)) {
                        JSONObject js = new JSONObject();
                        js.put("gid", qcidDict.get(ck).get("gid"));
                        js.put("title", qcidDict.get(ck).get("title"));
                        js.put("cid", qcidDict.get(ck).get("cid"));
                        quotaDict.getJSONArray("checkpoints").add(js);
                    }
                }

                List<String> qgidList = new ArrayList<>();
                List<XSSFRow> quotaContentList = new ArrayList<>();
                if (i != quotaIndexList.size() - 1) {
                    for (int ind = j; ind < quotaIndexList.get(i + 1); ind++) {
                        if (sheet.getRow(ind) != null) {
                            quotaContentList.add(sheet.getRow(ind));
                        }
                    }
                } else {
                    for (int ind = j; ind <= maxRow; ind++) {
                        if (sheet.getRow(ind) != null) {
                            quotaContentList.add(sheet.getRow(ind));
                        }
                    }
                }

                JSONArray contentList = new JSONArray();
                if ("普通配额".equals(quotaType)) {
                    for (XSSFRow quotaContent : quotaContentList) {
                        JSONObject contentDict = new JSONObject();
                        XSSFCell cell = quotaContent.getCell(3);
                        if (cell == null || StringUtils.isEmpty(cell.toString())) {
                            continue;
                        }
                        String qcid = cell.toString().split("__")[0];
                        String ocid = cell.toString().split("__")[1];
                        JSONObject q = qcidDict.get(qcid);
                        if (q == null || q.isEmpty()) {
                            errorMsg.add(String.format("文件内容格式错误（题目:%s 不存在）", qcid));
                            continue;
                        }
                        JSONObject option = getQuestionOptionByCid(qcid, ocid, qcidDict);
                        if (option == null || option.isEmpty()) {
                            errorMsg.add(String.format("文件内容格式错误（题目:%s 选项:%s 不存在）", qcid, ocid));
                            continue;
                        }

                        if (!qgidList.contains(q.getString("gid"))) {
                            qgidList.add(q.getString("gid"));
                        }
                        contentDict.put("qgid", q.get("gid"));
                        contentDict.put("ogid", option.get("gid"));
                        contentDict.put("title", option.get("title"));
                        contentDict.put("num", quotaContent.getCell(4) == null ? null : quotaContent
                                .getCell(4).toString());
                        contentList.add(contentDict);
                    }
                } else if ("交叉配额".equals(quotaType)) {
                    List<List<String>> x1 = new ArrayList<>();
                    List<List<String>> x2 = new ArrayList<>();
                    List<List<String>> y1 = new ArrayList<>();
                    List<List<String>> y2 = new ArrayList<>();
                    List<List<String>> optiongroupList = new ArrayList<>();
                    List<List<String>> row1 = new ArrayList<>();
                    List<List<String>> row2 = new ArrayList<>();
                    List<List<String>> col1 = new ArrayList<>();
                    List<List<String>> col2 = new ArrayList<>();

                    for (String cellItem : getCellList(quotaContentList.get(0), 5)) {
                        parseCellItem(cellItem, optiongroupList, qgidList, y1, col1, qcidDict, errorMsg);
                    }
                    for (String cellItem : getCellList(quotaContentList.get(1), 5)) {
                        parseCellItem(cellItem, optiongroupList, qgidList, y2, col2, qcidDict, errorMsg);
                    }
                    List<XSSFRow> quotaContentListSub = quotaContentList.subList(2, quotaContentList.size());
                    for (XSSFRow quotaContent : quotaContentListSub) {
                        if (quotaContent.getCell(3) != null) {
                            parseCellItem(quotaContent.getCell(3).toString(), optiongroupList, qgidList, x1, row1, qcidDict, errorMsg);
                        }
                        if (quotaContent.getCell(4) != null) {
                            parseCellItem(quotaContent.getCell(4).toString(), optiongroupList, qgidList, x2, row2, qcidDict, errorMsg);
                        }
                    }
                    for (int rowIndex = 0; rowIndex < quotaContentListSub.size(); rowIndex++) {
                        XSSFRow quotaContent = quotaContentListSub.get(rowIndex);
                        if ((quotaContent.getCell(3) == null || StringUtils.isEmpty(quotaContent.getCell(3).toString()))
                                && (quotaContent.getCell(4) == null || StringUtils.isEmpty(quotaContent.getCell(3).toString()))) {
                            if (CollectionUtils.isNotEmpty(x1) || CollectionUtils.isNotEmpty(x2)) {
                                continue;
                            }
                        }
                        if (CollectionUtils.isNotEmpty(x1) && rowIndex >= x1.size()) {
                            continue;
                        }
                        if (CollectionUtils.isNotEmpty(x2) && rowIndex >= x2.size()) {
                            continue;
                        }
                        if (CollectionUtils.isNotEmpty(x1) && rowIndex >= x1.size()) {
                            continue;
                        }

                        List<XSSFCell> cells = new ArrayList<>();
                        for (int ind = 5; ind < quotaContent.getLastCellNum(); ind++) {
                            cells.add(quotaContent.getCell(ind));
                        }
                        for (int colIndex = 0; colIndex < cells.size(); colIndex++) {
                            String num = cells.get(colIndex).toString();
                            if (CollectionUtils.isNotEmpty(y1) && colIndex >= y1.size()) {
                                continue;
                            }
                            if (CollectionUtils.isNotEmpty(y2) && colIndex >= y2.size()) {
                                continue;
                            }
                            contentList.add(NumberUtils.isDigits(num) ? Integer.parseInt(num) : -1);
                            if (CollectionUtils.isEmpty(y1) && CollectionUtils.isEmpty(y2)) {
                                break;
                            }
                        }
                    }

                    quotaDict.put("optiongroup_list", optiongroupList);
                    JSONObject avaQgidDict = new JSONObject();
                    quotaDict.put("row1", parseToXyFormat(row1, avaQgidDict, qgidDict));
                    quotaDict.put("row2", parseToXyFormat(row2, avaQgidDict, qgidDict));
                    quotaDict.put("col1", parseToXyFormat(col1, avaQgidDict, qgidDict));
                    quotaDict.put("col2", parseToXyFormat(col2, avaQgidDict, qgidDict));

                    if (CollectionUtils.isNotEmpty(errorMsg)) {
                        continue;
                    }

                    List<JSONObject> avaQgidList = new ArrayList<>();
                    for (String qgid : qgidList) {
                        JSONObject q = qgidDict.get(qgid);
                        JSONArray items = q.getJSONArray("items");
                        items = Optional.ofNullable(items).orElse(new JSONArray());
                        List<JSONObject> tmpOptions = new ArrayList<>();
                        for (int ind = 0; ind < items.size(); ind++) {
                            JSONObject oneJs = items.getJSONObject(ind);
                            if ("0".equals(oneJs.getString("type"))) {
                                tmpOptions.add(oneJs);
                            }
                        }
                        JSONObject tmpQgidDcit = avaQgidDict.getJSONObject(qgid);
                        tmpQgidDcit = Optional.ofNullable(tmpQgidDcit).orElse(new JSONObject());
                        if (tmpQgidDcit.isEmpty()) {
                            avaQgidDict.put(qgid, tmpQgidDcit);
                            JSONObject ckItemdict = new JSONObject();
                            ckItemdict.put("qcid", q.get("cid"));
                            ckItemdict.put("qtitle", q.get("title"));
                            ckItemdict.put("type", q.get("Q"));
                            ckItemdict.put("qgid", q.get("gid"));
                            genAvalQidItemDict(ckItemdict, tmpQgidDcit);
                            JSONArray children = tmpQgidDcit.getJSONArray("children");
                            for (JSONObject tmpo : tmpOptions) {
                                JSONObject opDict = new JSONObject();
                                opDict.put("composed", false);
                                opDict.put("gid", tmpo.get("gid"));
                                opDict.put("isShow", true);
                                opDict.put("pGid", qgid);
                                opDict.put("title", tmpo.get("title"));
                                opDict.put("type", "O");
                                children.add(opDict);
                            }
                        } else {
                            JSONArray children = tmpQgidDcit.getJSONArray("children");
                            children = Optional.ofNullable(children).orElse(new JSONArray());
                            tmpQgidDcit.put("children", children);
                            List<String> optionGroupUsedOptions = new ArrayList<>();
                            List<String> normalUsedOptions = new ArrayList<>();
                            for (int ind = 0; ind < children.size(); ind++) {
                                JSONObject tmpCd = children.getJSONObject(ind);
                                if (StringUtils.isNotEmpty(tmpCd.getString("composeGid"))) {
                                    optionGroupUsedOptions.add(tmpCd.getString("composeGid"));
                                } else {
                                    normalUsedOptions.add(tmpCd.getString("gid"));
                                }
                            }

                            for (JSONObject tmpo : tmpOptions) {
                                if (normalUsedOptions.contains(tmpo.getString("gid"))) {
                                    // 已经添加选项
                                    log.info("No need to do something");
                                } else if (optionGroupUsedOptions.contains(tmpo.getString("gid"))) {
                                    JSONObject opDict = new JSONObject();
                                    opDict.put("composed", true);
                                    opDict.put("gid", tmpo.get("gid"));
                                    opDict.put("isShow", false);
                                    opDict.put("pGid", qgid);
                                    opDict.put("title", tmpo.get("title"));
                                    opDict.put("type", "O");
                                    children.add(opDict);
                                } else {
                                    // 未添加的选项
                                    JSONObject opDict = new JSONObject();
                                    opDict.put("composed", false);
                                    opDict.put("gid", tmpo.get("gid"));
                                    opDict.put("isShow", true);
                                    opDict.put("pGid", qgid);
                                    opDict.put("title", tmpo.get("title"));
                                    opDict.put("type", "O");
                                    children.add(opDict);
                                }
                            }
                        }
                        avaQgidList.add(tmpQgidDcit);
                    }
                    quotaDict.put("ava_qgid_list", avaQgidList);
                }

                quotaDict.put("content_list", contentList);
                quotaDict.put("qgid_list", qgidList);
                quotaDict.put("qcid_list", qgidList.stream().map(qgid -> qgidDict.get(qgid).getString("cid"))
                        .collect(Collectors.toList()));
                quotaList.add(quotaDict);
            }
            return quotaList;
        } catch (IOException e) {
            log.error("Analysis excel error, e = ", e);
            throw new BusinessException(e.getMessage());
        }
    }

    private List<JSONObject> parseToXyFormat(List<List<String>> rowOrCol, JSONObject avaQgidDict, Map<String, JSONObject> qgidDict) {
        List<JSONObject> ret = new ArrayList<>();
        for (List<String> gs : rowOrCol) {
            JSONObject ckItemdict = new JSONObject();
            if (gs.size() > 1) {
                //  {
                //                    "qgid": 1,
                //                    "qtitle": "题目1",
                //                    "ogid": [[100,101],102],
                //                    "otitle": "组1"
                //                },
                List<String> newTitle = new ArrayList<>();
                List<Integer> ogids = new ArrayList<>();
                for (String tmpOpgidStr : gs) {
                    String[] gidList = tmpOpgidStr.split("__");
                    String qgid = gidList[0];
                    String ogid = gidList[1];
                    JSONObject q = qgidDict.get(qgid);
                    JSONObject option = getQuestionOptionByGid(qgid, ogid, qgidDict);
                    ckItemdict.put("qgid", Integer.parseInt(qgid));
                    ckItemdict.put("qtitle", q.get("title"));
                    ckItemdict.put("qcid", q.get("cid"));
                    newTitle.add(option.getString("title"));
                    ogids.add(option.getInteger("gid"));
                }
                ckItemdict.put("ogid", ogids);
                ckItemdict.put("otitle", String.format("组(%s)", String.join(",", newTitle)));
            } else {
                // {
                //                    "qgid": 1,
                //                    "qtitle": "题目1",
                //                    "ogid": 103,
                //                    "otitle": "选项4"
                //                }
                if (CollectionUtils.isNotEmpty(gs)) {
                    String tmpOpgidStr = gs.get(0);
                    String[] gidList = tmpOpgidStr.split("__");
                    String qgid = gidList[0];
                    String ogid = gidList[1];
                    JSONObject q = qgidDict.get(qgid);
                    JSONObject option = getQuestionOptionByGid(qgid, ogid, qgidDict);
                    ckItemdict.put("qgid", Integer.parseInt(qgid));
                    ckItemdict.put("qtitle", q.get("title"));
                    ckItemdict.put("qcid", q.get("cid"));
                    ckItemdict.put("ogid", option.getInteger("gid"));
                    ckItemdict.put("otitle", option.get("title"));
                }
            }
            ret.add(ckItemdict);

            // 根据交叉的项，生成前端ava_qgid_dict信息
            String qgidStr = ckItemdict.getString("qgid");
            JSONObject tmpQgidDict = avaQgidDict.getJSONObject(qgidStr);
            if (tmpQgidDict == null || tmpQgidDict.isEmpty()) {
                tmpQgidDict = new JSONObject();
                avaQgidDict.put(qgidStr, tmpQgidDict);
            }
            genAvalQidItemDict(ckItemdict, tmpQgidDict);
        }
        return ret;
    }

    private void genAvalQidItemDict(JSONObject ckItemdict, JSONObject tmpQgidDict) {
        // 根据交叉的项，生成前端ava_qgid_dict信息
        String qgid = ckItemdict.getString("qgid");
        tmpQgidDict.put("gid", qgid);
        tmpQgidDict.put("cid", ckItemdict.get("qcid"));
        tmpQgidDict.put("isShow", true);
        tmpQgidDict.put("title", ckItemdict.get("qtitle"));
        tmpQgidDict.put("type", "Q");
        JSONArray children = tmpQgidDict.getJSONArray("children");
        if (children == null || children.isEmpty()) {
            children = new JSONArray();
            tmpQgidDict.put("children", children);
        }
        Object ogids = ckItemdict.get("ogid");
        if (ogids != null) {
            if (ogids instanceof List) {
                List<Object> tiledOgids = new ArrayList<>();
                getOpitonList(ogids, tiledOgids);
                JSONObject opDict = new JSONObject();
                opDict.put("composed", false);
                opDict.put("gid", tiledOgids.stream().map(String::valueOf)
                        .collect(Collectors.joining(",")));
                opDict.put("composeGid", ogids);
                opDict.put("isShow", false);
                opDict.put("pGid", qgid);
                opDict.put("title", ckItemdict.get("otitle"));
                opDict.put("type", "O");
                children.add(opDict);
            } else {
                JSONObject opDict = new JSONObject();
                opDict.put("composed", false);
                opDict.put("gid", ogids);
                opDict.put("isShow", false);
                opDict.put("pGid", qgid);
                opDict.put("title", ckItemdict.get("otitle"));
                opDict.put("type", "O");
                children.add(opDict);
            }
        }
    }

    private void parseCellItem(String cellItem, List<List<String>> optiongroupList, List<String> qgidList, List<List<String>> y,
                               List<List<String>> cols, Map<String, JSONObject> qcidDict, List<String> errorMsg) {
        if (StringUtils.isNotEmpty(cellItem)) {
            List<String> gs = new ArrayList<>();
            for (String cidStr : cellItem.split(",")) {
                if (StringUtils.isNotEmpty(cidStr)) {
                    String[] cidStrs = cidStr.split("__");
                    if (cidStrs.length < 2) {
                        continue;
                    }
                    String qcid = cidStrs[0];
                    String ocid = cidStrs[1];
                    JSONObject q = qcidDict.get(qcid);
                    if (q == null || q.isEmpty()) {
                        errorMsg.add(String.format("文件内容格式错误（题目:%s 不存在）", qcid));
                        continue;
                    }
                    JSONObject option = getQuestionOptionByCid(qcid, ocid, qcidDict);
                    if (option == null || option.isEmpty()) {
                        errorMsg.add(String.format("文件内容格式错误（题目:%s 选项:%s 不存在）", qcid, ocid));
                        continue;
                    }

                    if (!qgidList.contains(q.getString("gid"))) {
                        qgidList.add(q.getString("gid"));
                    }
                    gs.add(String.format("%s__%s", q.getString("gid"), option.getString("gid")));
                }
            }

            if (CollectionUtils.isNotEmpty(gs)) {
                y.add(gs);
                if (!cols.contains(gs)) {
                    cols.add(gs);
                }
            }
            if (CollectionUtils.isNotEmpty(gs) && gs.size() > 1 && optiongroupList.contains(gs)) {
                optiongroupList.add(gs);
            }
        }
    }

    private List<String> getCellList(XSSFRow row, int start) {
        List<String> cellList = new ArrayList<>();
        for (int ind = start; ind <= row.getLastCellNum(); ind++) {
            cellList.add(row.getCell(ind) == null ? "" : row.getCell(ind).toString());
        }
        return cellList;
    }

    private void writeCrossQuota(List<List<String>> allData, QdesQuota quotaItem, Map<String, JSONObject> qgidDict) {
        List<String> lineData = new ArrayList<>();
        lineData.add(quotaItem.getName());
        lineData.add("交叉配额");
        allData.add(lineData);
        JSONArray checkpoints;
        List<JSONObject> quotaCheckitemList = new ArrayList<>();
        String checkpointStr = "";
        if (StringUtils.isNotEmpty(quotaItem.getQuotaCondList()) && CollectionUtils.isNotEmpty(JSONArray.parseArray(quotaItem.getQuotaCondList(), String.class))) {
            List<QuotaCondition> quotaConditions = surveyGrpcClient.getQuotaConditions(JSONArray.parseArray(quotaItem.getQuotaCondList(), String.class).subList(0, 1));
            QuotaCondition crossQuotaCondition = quotaConditions.get(0);
            checkpoints = StringUtils.isEmpty(crossQuotaCondition.getCheckpoints()) ? new JSONArray() : JSONArray
                    .parseArray(crossQuotaCondition.getCheckpoints());
            List<String> chklist = new ArrayList<>();
            if (!checkpoints.isEmpty()) {
                for (int i = 0; i < checkpoints.size(); i++) {
                    JSONObject ckdict = checkpoints.getJSONObject(i);
                    chklist.add(qgidDict.get(ckdict.getString("gid")).getString("cid"));
                }
            }
            checkpointStr = String.join(", ", chklist);
            quotaCheckitemList = StringUtils.isEmpty(crossQuotaCondition.getQuotaCheckitemList()) ? new ArrayList<>() :
                    JSONArray.parseArray(crossQuotaCondition.getQuotaCheckitemList(), JSONObject.class);
        }
        lineData.add(checkpointStr);

        List<JSONObject> row1List = getXyItemsByCrossOne(JSONArray.parseArray(quotaItem.getRow1()));
        List<JSONObject> row2List = getXyItemsByCrossOne(JSONArray.parseArray(quotaItem.getRow2()));
        List<JSONObject> col1List = getXyItemsByCrossOne(JSONArray.parseArray(quotaItem.getCol1()));
        List<JSONObject> col2List = getXyItemsByCrossOne(JSONArray.parseArray(quotaItem.getCol2()));

        List<List<JSONObject>> rowDika = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(row1List) && CollectionUtils.isNotEmpty(row2List)) {
            List<List<JSONObject>> unionGidLists = new ArrayList<>();
            unionGidLists.add(row1List);
            unionGidLists.add(row2List);
            rowDika = Lists.cartesianProduct(unionGidLists);
        } else {
            if (CollectionUtils.isNotEmpty(row2List)) {
                for (JSONObject row : row2List) {
                    List<JSONObject> rows = new ArrayList<>();
                    rows.add(row);
                    rows.add(new JSONObject());
                    rowDika.add(rows);
                }
            } else if (CollectionUtils.isNotEmpty(row1List)) {
                for (JSONObject row : row1List) {
                    List<JSONObject> rows = new ArrayList<>();
                    rows.add(row);
                    rows.add(new JSONObject());
                    rowDika.add(rows);
                }
            }
        }
        List<List<JSONObject>> colDika = new ArrayList<>();
        if (CollectionUtils.isNotEmpty(col1List) && CollectionUtils.isNotEmpty(col2List)) {
            List<List<JSONObject>> unionGidLists = new ArrayList<>();
            unionGidLists.add(col1List);
            unionGidLists.add(col2List);
            colDika = Lists.cartesianProduct(unionGidLists);
        } else {
            if (CollectionUtils.isNotEmpty(col2List)) {
                for (JSONObject col : col2List) {
                    List<JSONObject> cols = new ArrayList<>();
                    cols.add(col);
                    cols.add(new JSONObject());
                    colDika.add(cols);
                }
            } else if (CollectionUtils.isNotEmpty(col1List)) {
                for (JSONObject col : col1List) {
                    List<JSONObject> cols = new ArrayList<>();
                    cols.add(col);
                    cols.add(new JSONObject());
                    colDika.add(cols);
                }
            }
        }

        int num = 0;
        List<String> newLineList = new ArrayList<>();
        allData.add(newLineList);
        if (CollectionUtils.isNotEmpty(colDika)) {
            for (List<JSONObject> crossY : colDika) {
                JSONObject item1 = crossY.get(0);
                JSONObject item2 = crossY.get(1);
                if (item1 != null && !item1.isEmpty()) {
                    String qgid = item1.getString("qgid");
                    String tablestr = qgidOptionCombis(qgid, item1, qgidDict);
                    setIndexStr(lineData, 5 + num, tablestr);
                }
                if (item2 != null && !item2.isEmpty()) {
                    String qgid = item2.getString("qgid");
                    String tablestr = qgidOptionCombis(qgid, item2, qgidDict);
                    setIndexStr(newLineList, 5 + num, tablestr);
                }
                num += 1;
            }
        }

        if (CollectionUtils.isNotEmpty(rowDika)) {
            for (List<JSONObject> crossX : rowDika) {
                List<String> newRowLine = new ArrayList<>();
                allData.add(newRowLine);
                JSONObject item1 = crossX.get(0);
                JSONObject item2 = crossX.get(1);
                if (item1 != null && !item1.isEmpty()) {
                    String qgid = item1.getString("qgid");
                    String tablestr = qgidOptionCombis(qgid, item1, qgidDict);
                    setIndexStr(newRowLine, 3, tablestr);
                }
                if (item2 != null && !item2.isEmpty()) {
                    String qgid = item2.getString("qgid");
                    String tablestr = qgidOptionCombis(qgid, item2, qgidDict);
                    setIndexStr(newRowLine, 4, tablestr);
                }

                List<JSONObject> crossCondIdsRow;
                if (colDika != null && !colDika.isEmpty()) {
                    crossCondIdsRow = quotaCheckitemList.subList(0, colDika.size());
                } else {
                    crossCondIdsRow = quotaCheckitemList.subList(0, 1);
                }
                int col = 5;
                if (CollectionUtils.isNotEmpty(crossCondIdsRow)) {
                    crossCondIdsRow = new ArrayList<>(crossCondIdsRow);
                }
                for (JSONObject crossCond : crossCondIdsRow) {
                    setIndexStr(newRowLine, col, crossCond.getString("num"));
                    quotaCheckitemList.remove(crossCond);
                    col += 1;
                }
            }
        } else {
            // 有列无行，直接写列
            List<String> newRowLine = new ArrayList<>();
            allData.add(newRowLine);
            if (colDika != null && !colDika.isEmpty()) {
                int col = 5;
                for (JSONObject crossCond : quotaCheckitemList) {
                    setIndexStr(newRowLine, col, crossCond.getString("num"));
                    col += 1;
                }
            }
            allData.add(Collections.emptyList());
        }
        allData.add(Collections.emptyList());
    }

    private void setIndexStr(List<String> dataList, int index, String val) {
        if (dataList.size() < index + 1) {
            for (int i = dataList.size(); i <= index + 1; i++) {
                dataList.add("");
            }
        }
        dataList.set(index, val);
    }

    private void setRowItemData(JSONObject item1, JSONObject item2, List<String> lineData, List<List<String>> allData, Map<String, JSONObject> qgidDict) {
        if (item1 != null && !item1.isEmpty()) {
            String qgid = item1.getString("qgid");
            String tablestr = qgidOptionCombis(qgid, item1, qgidDict);
            //lineData.set()

        }
    }

    private String qgidOptionCombis(String qgid, JSONObject item, Map<String, JSONObject> qgidDict) {
        Object ogid = item.get("ogid");
        boolean isOptionGroup = ogid instanceof List;
        List<String> options;
        if (isOptionGroup) {
            options = JSONArray.parseArray(item.getString("ogid"), String.class);
        } else {
            options = Collections.singletonList(item.getString("ogid"));
        }
        JSONObject q = qgidDict.get(qgid);
        List<String> tables = new ArrayList<>();
        for (String optionGid : options) {
            JSONObject option = getQuestionOptionByGid(qgid, optionGid, qgidDict);
            String cellStr = q.getString("cid") + "__" + option.getString("oid");
            tables.add(cellStr);
        }
        return String.join(", ", tables);
    }

    private List<JSONObject> getXyItemsByCrossOne(JSONArray crossOne) {
        // 解析前端给到的交叉项结构
        //    :param cross_one:
        //    [
        //        {
        //            "qgid":1,
        //            "ogid": [[1,2],3],
        //            "otitle":"组1",
        //        },{
        //            "qgid": 1,
        //            "ogid": 4,
        //            "otitle": "选项4",
        //        }
        //    ]
        //    :return:
        //    [{
        //            'qgid': 1,
        //            'ogid': [1,2,3]
        //            "otitle":"组1",
        //        }, {
        //            'qgid': 1,
        //            'ogid': '4',
        //            'otitle': 'd',
        //        }
        //    ]
        List<JSONObject> ret = new ArrayList<>();
        for (int i = 0; i < crossOne.size(); i++) {
            JSONObject item = crossOne.getJSONObject(i);
            if (item.get("ogid") instanceof List) {
                List<Object> tiledOgids = new ArrayList<>();
                getOpitonList(item.get("ogid"), tiledOgids);
                item.put("ogid", tiledOgids);
            }
            ret.add(item);
        }
        return ret;
    }

    private void getOpitonList(Object ogid, List<Object> tiledOgids) {
        if (ogid instanceof List) {
            for (Object item : (List<?>) ogid) {
                getOpitonList(item, tiledOgids);
            }
        } else {
            tiledOgids.add(ogid);
        }
    }

    private void writeNormalQuota(List<List<String>> allData, QdesQuota quota, Map<String, JSONObject> qgidDict) {
        List<String> lineData = new ArrayList<>();
        lineData.add(quota.getName());
        lineData.add("普通配额");
        if (StringUtils.isEmpty(quota.getQuotaCondList())) {
            return;
        }

        List<QuotaCondition> quotaConditions = surveyGrpcClient.getQuotaConditions(JSONArray.parseArray(quota.getQuotaCondList(), String.class));
        for (int ind = 0; ind < quotaConditions.size(); ind++) {
            QuotaCondition quotaCondition = quotaConditions.get(ind);
            JSONArray qgidList = Optional.ofNullable(JSONArray.parseArray(quotaCondition.getQgidList())).orElse(new JSONArray());
            String qgid = qgidList.isEmpty() ? "" : qgidList.getJSONObject(0).getString("qgid");
            JSONArray checkpoints = Optional.ofNullable(JSONArray.parseArray(quotaCondition.getCheckpoints())).orElse(new JSONArray());
            List<String> chklist = new ArrayList<>();
            if (!checkpoints.isEmpty()) {
                for (int i = 0; i < checkpoints.size(); i++) {
                    JSONObject ckdict = checkpoints.getJSONObject(i);
                    chklist.add(qgidDict.get(ckdict.getString("gid")).getString("cid"));
                }
            }

            JSONArray checkitemList = Optional.ofNullable(JSONArray.parseArray(quotaCondition.getQuotaCheckitemList())).orElse(new JSONArray());
            JSONObject quotaCheckitem = Optional.ofNullable(checkitemList.getJSONObject(0)).orElse(new JSONObject());
            String optionGid = quotaCheckitem.getJSONArray("optionList") == null ? "" : quotaCheckitem
                    .getJSONArray("optionList").getJSONObject(0).getString("ogid");

            JSONObject q = qgidDict.get(qgid);
            JSONObject option = getQuestionOptionByGid(qgid, optionGid, qgidDict);
            String cellStr = q.getString("cid") + "__" + option.getString("oid");
            if (ind == 0) {
                lineData.add(String.join(", ", chklist));
                lineData.add(cellStr);
                lineData.add(quotaCheckitem.getString("num"));
                allData.add(lineData);
            } else {
                List<String> newLineData = new ArrayList<>();
                setIndexStr(newLineData, 2, String.join(", ", chklist));
                setIndexStr(newLineData, 3, cellStr);
                setIndexStr(newLineData, 4, quotaCheckitem.getString("num"));
                allData.add(newLineData);
            }
        }
    }

    private JSONObject getQuestionOptionByGid(String qgid, String ogid, Map<String, JSONObject> qgidDict) {
        // 根据ogid获取选项信息
        JSONObject question = qgidDict.get(qgid);
        if (question != null) {
            JSONArray items = question.getJSONArray("items");
            if (items != null && !items.isEmpty()) {
                List<JSONObject> options = new ArrayList<>();
                for (int i = 0; i < items.size(); i++) {
                    JSONObject item = items.getJSONObject(i);
                    if ("O".equals(item.getString("type"))) {
                        options.add(item);
                    }
                }
                Map<String, JSONObject> ogidDict = new HashMap<>();
                for (JSONObject o : options) {
                    ogidDict.put(o.getString("gid"), o);
                }
                if (StringUtils.isNotEmpty(ogid) && ogid.contains("_")) {
                    ogid = ogid.split("_")[1];
                }
                return ogidDict.get(ogid); // dict type: E.g. {type: "O", gid: 5, title: "a2", seq: 5, oid: "12", is_open: false}
            }
        }
        return new JSONObject();
    }

    private List<JSONObject> getQuestionCidGidDict(QdesProject project) {
        // 根据project获取题目信息
        QdesQstruct qStruct = qdesQstructMapper.selectById(project.getStructId());
        if (qStruct == null) {
            return new ArrayList<>();
        }
        // 获取questions
        LambdaQueryWrapper<QdesQuestion> questionWrapper = Wrappers.lambdaQuery();
        questionWrapper.eq(QdesQuestion::getStructId, qStruct.getId());
        List<QdesQuestion> qdesQuestions = qdesQuestionMapper.selectList(questionWrapper);
        qdesProjectService.fillQstructData(qStruct, qdesQuestions);
        JSONObject data = JSONObject.parseObject(qStruct.getData());
        // 选项关联，导出项目，去掉被关联过来的选项
        return qdesProjectService.walkQstructQuestions(data);
    }

    private JSONObject getQuestionOptionByCid(String qgid, String ogid, Map<String, JSONObject> qgidDict) {
        // 根据ogid获取选项信息
        JSONObject question = qgidDict.get(qgid);
        if (question != null) {
            JSONArray items = question.getJSONArray("items");
            if (items != null && !items.isEmpty()) {
                List<JSONObject> options = new ArrayList<>();
                for (int i = 0; i < items.size(); i++) {
                    JSONObject item = items.getJSONObject(i);
                    if ("O".equals(item.getString("type"))) {
                        options.add(item);
                    }
                }
                Map<String, JSONObject> ogidDict = new HashMap<>();
                for (JSONObject o : options) {
                    ogidDict.put(o.getString("oid"), o);
                }
                return ogidDict.get(ogid); // dict type: E.g. {type: "O", gid: 5, title: "a2", seq: 5, oid: "12", is_open: false}
            }
        }
        return new JSONObject();
    }

    private void exportWithDynamicData(String fileName, List<List<String>> exportData, String sheetName, HttpServletResponse response) {
        File exportFile = ExcelExportUtil.exportNoHeadFile(fileName, exportData, sheetName);
        Assert.notNull(exportFile, "exportFile null");
        //上传到oss
        ExcelExportUtil.downloadTemplate(exportFile.getPath(), fileName, ExcelExportUtil.XLSX_SUFFIX, response);
        exportFile.delete();
    }
}